package com.alangyun.remind.service.excel;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alangyun.remind.utils.Utility;

public class PoiUtils {
	private static final Logger logger = LoggerFactory.getLogger(PoiUtils.class);
	
	public static String getStringValue(Cell cell) {
		if(cell==null)
			return "";

        logger.debug("正在读取String列[{}]:", cell.getColumnIndex());
		String ret = "";
		CellType type= cell.getCellType();
		switch(type) {
		case _NONE:
		case BLANK:
		case ERROR:
			ret= "";
			break;
		case NUMERIC:
			ret= Long.toString((long) cell.getNumericCellValue());
			break;
		case STRING:
			ret = cell.getStringCellValue();
			break;
		case FORMULA:
			FormulaEvaluator evaluator=cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
			ret=new org.apache.poi.ss.usermodel.DataFormatter().formatCellValue(cell, evaluator);

			break;
		case BOOLEAN:
			ret= cell.getBooleanCellValue()+"";
			break;
		}


		logger.debug("value(String) {}", ret);

		return ret;
	}

	public static int getIntValue(Cell cell) {
		if(cell==null)
			return 0;

        logger.debug("正在读取Int列[{}]:", cell.getColumnIndex());
		int ret= 0;
		CellType type= cell.getCellType();
		switch(type) {
		case _NONE:
		case BLANK:
		case ERROR:
			break;
		case NUMERIC:
			ret= (int)cell.getNumericCellValue();
			break;
		case STRING:
			String temp= cell.getStringCellValue();
			temp= temp==null?"":temp.trim();
            logger.debug("({})", temp);
			if(!temp.isEmpty() && Utility.isNumber(temp))
				ret= (int)Double.parseDouble(temp);
			break;
		case FORMULA:
			FormulaEvaluator evaluator=cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
			String temp2=new org.apache.poi.ss.usermodel.DataFormatter().formatCellValue(cell, evaluator);
			temp2= temp2==null?"":temp2.trim();
			if(!temp2.isEmpty() && Utility.isNumber(temp2))
				ret= Integer.parseInt(temp2);
            break;
		case BOOLEAN:
			ret= cell.getBooleanCellValue()?1:0;
			break;
		}
		logger.debug("value(Int) {}", ret);

		return ret;
	}

	public static boolean getBooleanValue(Cell cell) {
		if(cell==null)
			return false;

        logger.debug("正在读取Boolean列[{}]:", cell.getColumnIndex());
		boolean ret= false;
		CellType type= cell.getCellType();
		switch(type) {
		case _NONE:
		case BLANK:
		case ERROR:
		case FORMULA:
			break;
		case NUMERIC:
			ret = cell.getNumericCellValue() > 0;
			break;
		case STRING:
			String temp= cell.getStringCellValue();
			temp= temp==null?"":temp.trim();
            logger.debug("({})", temp);
			if(!temp.isEmpty())
				ret= "有".equals(temp)||"是".equals(temp);
			break;
		case BOOLEAN:
			ret = cell.getBooleanCellValue();
			break;
		}
		logger.debug("value(Boolean) {}", ret);

		return ret;
	}

	public static double getDoubleValue(Cell cell) {
		if(cell==null)
			return 0.0;

        logger.debug("正在读取Double列[{}]:", cell.getColumnIndex());
		double ret= 0;
		CellType type= cell.getCellType();
		switch(type) {
		case _NONE:
		case BLANK:
		case ERROR:
			break;
		case NUMERIC:
			ret = cell.getNumericCellValue();
			break;
		case STRING:
			String temp= cell.getStringCellValue();
			if(temp!=null && !temp.isEmpty() && Utility.isNumber(temp))
				ret= Double.parseDouble(temp);
			break;
		case FORMULA:
			FormulaEvaluator evaluator=cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
			String temp2=new org.apache.poi.ss.usermodel.DataFormatter().formatCellValue(cell, evaluator);

			if(temp2!=null && !temp2.isEmpty() && Utility.isNumber(temp2))
				ret= Double.parseDouble(temp2);
			else
				ret= 0;
			break;
		case BOOLEAN:
			ret= cell.getBooleanCellValue()?1:0;
			break;
		}
		logger.debug("value(Double) {}", ret);

		return ret;
	}

	public static Date getDatetimeValue(Cell cell) {
		if(cell == null)
			return null;

        logger.debug("正在读取Date列[{}]:", cell.getColumnIndex());
		Date ret = null;
		CellType type = cell.getCellType();
		switch(type) {
		case _NONE:
		case BLANK:
		case ERROR:
		case FORMULA:
		case BOOLEAN:
			break;
		case NUMERIC:
			String numStr = String.format("%.0f", cell.getNumericCellValue());
			if(numStr.length() == 8) {
				try {
					ret = new SimpleDateFormat("yyyyMMdd").parse(numStr);
				} catch (ParseException ex) {
					logger.warn("无法解析数值日期: {}", numStr);
				}
            }
			break;
		case STRING:
			String temp = cell.getStringCellValue();
			if(temp!=null && !temp.isEmpty()) {
				try {
				ret = Utility.customToDate(temp);//.toDatetime(temp, format);
				}catch(Exception ignored){}
			}
			break;
		}
        logger.debug("value(Date) {}", ret);
		
		return ret;
	}
}
